﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using EnvironmentalMonitor.Support.Hibernate.Manage;
using EnvironmentalMonitor.Support.Module.Environmental;
using EnvironmentalMonitor.Support.Module.Manage;
using EnvironmentalMonitor.Support.Module;
using EnvironmentalMonitor.Support.Resource;

namespace EnvironmentalMonitor.Support.Hibernate.Environmental
{
    /// <summary>
    /// 岗位和设备关系持久化类
    /// </summary>
    public class PostMachineHibernate
    {
        /// <summary>
        /// 所有字段
        /// </summary>
        /// <param name="prefix">前缀</param>
        /// <returns>所有字段</returns>
        public string Asterisk(string prefix)
        {
            string result = string.Empty;

            result = string.Format("{0}[guid], {0}[insert_user_id], {0}[insert_time], {0}[update_user_id], {0}[update_time], {0}[remark], {0}[validity], {0}[post_id], {0}[machine_id]", prefix);

            return result;
        }

        /// <summary>
        /// 数据解析
        /// </summary>
        /// <param name="values">数据</param>
        /// <returns>岗位和设备关系</returns>
        public PostMachine Parse(object[] values)
        {
            PostMachine result = new PostMachine();

            MachineHibernate machineHibernate = new MachineHibernate();
            int total = 0;
            List<Machine> machines = machineHibernate.Query(1, int.MaxValue, ref total);
            PostHibernate postHibernate = new PostHibernate();
            List<Post> posts = postHibernate.Query(1, int.MaxValue, ref total);
            try
            {
                result = DatabaseHibernate.ParseModule(result, values) as PostMachine;

                result.PostId = DatabaseHibernate.ParseString(values[7]);
                result.MachineId = DatabaseHibernate.ParseString(values[8]);

                for (int i = 0; i < machines.Count; i++)
                {
                    if (result.MachineId == machines[i].Guid)
                    {
                        result.Machine = machines[i];
                        break;
                    }
                }
                for (int i = 0; i < posts.Count; i++)
                {
                    if (result.PostId == posts[i].Guid)
                    {
                        result.Post = posts[i];
                        break;
                    }
                }
            }
            catch (Exception exception)
            {
                result = null;
                EnvironmentalMonitor.Support.Resource.Variable.Logger.Log(exception);
            }

            return result;
        }

        /// <summary>
        /// 数据解析
        /// </summary>
        /// <param name="values">数据集合</param>
        /// <returns>岗位和设备关系集合</returns>
        public List<PostMachine> Parse(List<object[]> values)
        {
            List<PostMachine> results = new List<PostMachine>();

            if (values != null)
            {
                for (int i = 0; i < values.Count; i++)
                {
                    PostMachine value = this.Parse(values[i]);
                    results.Add(value);
                }
            }

            return results;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="page">页码</param>
        /// <param name="rows">每页行数</param>
        /// <param name="total">总数</param>
        /// <returns>岗位设备关系集合</returns>
        public List<PostMachine> Query(int page, int rows, ref int total)
        {
            List<PostMachine> results = new List<PostMachine>();

            string sql = string.Format("select {0} from e_post_machine as pmt, m_post as pt, (select mt_a.*, [rt_a].[name] as room_name from e_machine as mt_a left join e_room as rt_a on [mt_a].[room_id] = [rt_a].[guid]) as mt where [pmt].[post_id] = [pt].[guid] and [pmt].[machine_id] = [mt].[guid] order by [pt].[name], [mt].[room_name], [mt].[name]", this.Asterisk("[pmt]."));
            List<Parameter> parameters = new List<Parameter>();
            DatabaseHibernate hibernate = new DatabaseHibernate();

            string countSql = string.Format("select count(*) from ({0})", sql);
            parameters.Clear();

            List<object[]> values = hibernate.Read(Variable.Link, countSql, parameters);

            if ((values != null) && (values.Count == 1))
            {
                object[] moduleValues = values[0];
                total = DatabaseHibernate.ParseInt(moduleValues[0]);
            }

            string querySql = sql;

            parameters.Clear();

            values = hibernate.Read(Variable.Link, querySql, parameters, page, rows);

            results = this.Parse(values);

            return results;
        }

        /// <summary>
        /// 通过岗位查询
        /// </summary>
        /// <param name="postGuid">岗位主键</param>
        /// <returns>岗位和设备关系集合</returns>
        public List<PostMachine> QueryByPost(string postGuid)
        {
            List<PostMachine> results = new List<PostMachine>();

            string sql = string.Format("select {0} from (select mt_a.*, [rt_a].[name] as room_name from e_machine as mt_a left join e_room as rt_a on [mt_a].[room_id] = [rt_a].[guid]) as mt, e_post_machine as pmt, m_post as pt where [pmt].[post_id] = [pt].[guid] and [pmt].[machine_id] = [mt].[guid] and [pt].[guid] = :postGuid order by [pt].[name], [mt].[room_name], [mt].[name]", this.Asterisk("[pmt]."));
            List<Parameter> parameters = new List<Parameter>();

            parameters.Add(new Parameter("postGuid", DatabaseHibernate.Parameter(postGuid)));

            DatabaseHibernate hibernate = new DatabaseHibernate();

            List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);

            results = this.Parse(values);

            return results;
        }

        /// <summary>
        /// 通过用户查询
        /// </summary>
        /// <param name="userGuid">用户编号</param>
        /// <returns>岗位和设备关系集合</returns>
        public List<PostMachine> QueryByUser(string userGuid)
        {
            List<PostMachine> results = new List<PostMachine>();

            string sql = string.Format("select distinct {0} from m_post_user as a, e_post_machine as b, e_machine as c where [a].[user_id] = :userGuid and [a].[validity] = 1 and [b].[validity] = 1 and [c].[validity] = 1 and [a].[post_id] = [b].[post_id] and [b].[machine_id] = [c].[guid]", this.Asterisk("[b]."));
            List<Parameter> parameters = new List<Parameter>();
            DatabaseHibernate hibernate = new DatabaseHibernate();

            parameters.Clear();
            parameters.Add(new Parameter("userGuid", DatabaseHibernate.Parameter(userGuid)));

            List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);

            results = this.Parse(values);

            return results;
        }

        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="value">值</param>
        /// <returns>结果</returns>
        public bool Insert(PostMachine value)
        {
            bool result = false;

            string sql = string.Format("insert into e_post_machine ({0}) values (:guid, :insert_user_id, :insert_time, :update_user_id, :update_time, :remark, :validity, :post_id, :machine_id)", this.Asterisk(""));
            List<Parameter> parameters = new List<Parameter>();

            parameters.Add(new Parameter("guid", DatabaseHibernate.Parameter(DatabaseHibernate.GUID())));
            parameters.Add(new Parameter("insert_user_id", DatabaseHibernate.Parameter(value.InsertUserId)));
            parameters.Add(new Parameter("insert_time", DatabaseHibernate.Parameter(value.InsertTime)));
            parameters.Add(new Parameter("update_user_id", DatabaseHibernate.Parameter(value.UpdateUserId)));
            parameters.Add(new Parameter("update_time", DatabaseHibernate.Parameter(value.UpdateTime)));
            parameters.Add(new Parameter("remark", DatabaseHibernate.Parameter(value.Remark)));
            parameters.Add(new Parameter("validity", DatabaseHibernate.Parameter(value.Validity)));

            parameters.Add(new Parameter("post_id", DatabaseHibernate.Parameter(value.PostId)));
            parameters.Add(new Parameter("machine_id", DatabaseHibernate.Parameter(value.MachineId)));

            DatabaseHibernate hibernate = new DatabaseHibernate();

            result = hibernate.Write(Variable.Link, sql, parameters);

            return result;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="guid">主键</param>
        /// <returns>结果</returns>
        public bool Delete(string guid)
        {
            bool result = false;

            string sql = string.Format("delete from e_post_machine as t where [t].[guid] = '{0}'", guid);
            List<Parameter> parameters = new List<Parameter>();

            DatabaseHibernate hibernate = new DatabaseHibernate();

            result = hibernate.Write(Variable.Link, sql, parameters);

            return result;
        }
    }
}
